package edu.isi.pedwork.data;


import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Hashtable;
import java.util.Iterator;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class ExcelReader {
	File file = null;
	public ExcelReader(File file)
	{
		this.file = file;
	}
	public List<List<HSSFCell>> readExcel() throws IOException
	{
		String filename = file.toString();
		List<List<HSSFCell>> sheetData = new ArrayList<List<HSSFCell>>();
		FileInputStream fis = null;
		try {
			//Create a FileInputStream that will be used to read the excel;
			fis = new FileInputStream(filename);
			//Create an excel workbook from the file system
			HSSFWorkbook workbook = new HSSFWorkbook(fis);
			//Get the first sheet on the workbook
			HSSFSheet sheet = workbook.getSheetAt(0);
			
			Iterator<?> rows = sheet.rowIterator();

			while(rows.hasNext()){
				HSSFRow row = (HSSFRow)rows.next();
				Iterator<?> cells = row.cellIterator();
				
				List<HSSFCell> data = new ArrayList<HSSFCell>();
				
				while(cells.hasNext()){
					HSSFCell cell = (HSSFCell)cells.next();
					data.add(cell);
				}
				sheetData.add(data);
			}
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally{
			if(fis!=null)	fis.close();
		}
		return sheetData;
	}
	
	public Hashtable<Comparable, Integer> getExcelHeaderData(List sheetData, int index)
	{
		Hashtable<Comparable, Integer> header = new Hashtable<Comparable, Integer>();
		
		List list = (List)sheetData.get(index);
		for(int j = 0; j<list.size(); j++){
			HSSFCell cell = (HSSFCell)list.get(j);
			
			if(cell.getCellType() == cell.CELL_TYPE_STRING)
				header.put(cell.getStringCellValue(), j);
			else if(cell.getCellType() == cell.CELL_TYPE_NUMERIC)
				header.put(cell.getNumericCellValue(), j);
		}
		
		return header;
	}
	
	public ArrayList getExcelDataAtIndex(List sheetData, int index, ArrayList responseList)
	{
		for(int i=1; i<sheetData.size(); i++)
		{
			List list = (List)sheetData.get(i);
			HSSFCell cell = (HSSFCell)list.get(index);
			
			if(cell.getCellType() == cell.CELL_TYPE_STRING)
				responseList.add(cell.getStringCellValue());
			else if(cell.getCellType() == cell.CELL_TYPE_NUMERIC)
				responseList.add(cell.getNumericCellValue());
			else if(cell.getCellType() == cell.CELL_TYPE_BLANK)
				responseList.add("0.0");
			else
				responseList.add("0.0");
		}
		return responseList;
	}
	
	public int getHeaderIndex(Hashtable header, String factor)
	{
		int index = (Integer)header.get(factor); 
		return index;
	}
}
